/* @formatter:off
 * Copyright (c) 2011 Lucas <lucas03093@gmail.com>
 * 
 * 
 * This program is free software: you can redistribute it and/or modify it under
 * the terms of the GNU General Public License as published by the Free Software
 * Foundation, either version 3 of the License, or (at your option) any later
 * version.
 * 
 * This program is distributed in the hope that it will be useful, but WITHOUT
 * ANY WARRANTY; without even the implied warranty of MERCHANTABILITY or FITNESS
 * FOR A PARTICULAR PURPOSE. See the GNU General Public License for more
 * details.
 * 
 * You should have received a copy of the GNU General Public License along with
 * this program. If not, see <http://www.gnu.org/licenses/>.
 */
/* @formatter:on */

package nu.korkad.daytracker.database;

import java.util.Locale;

import nu.korkad.daytracker.settings.Config;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteMisuseException;
import android.database.sqlite.SQLiteOpenHelper;
import android.database.sqlite.SQLiteStatement;
import android.util.Log;

public class DataHelper
{
    // Database info
    private static final String   DATABASE_NAME    = Config.DATABASE_NAME;
    private static final int      DATABASE_VERSION = Config.DATABASE_VERSION;
    private static final String   TABLE_TRACKER    = Config.DATABASE_TABLE;
    
    // Global
    private final Context         context;
    private final SQLiteDatabase  db;
    
    // Statements
    private final SQLiteStatement insertStmt;
    private static final String   INSERT           = "INSERT INTO `"
                                                           + TABLE_TRACKER
                                                           + "` (`company`, `checkin`, `checkout`, `text`, `unread`) VALUES (?, ?, ?, ?, ?)";
    
    private final SQLiteStatement updateStmtCheckIn;
    private static final String   UPDATE_CHECKIN   = "UPDATE `" + TABLE_TRACKER + "` SET `checkin` = ? WHERE `id` = ?";
    
    private final SQLiteStatement updateStmtCheckOut;
    private static final String   UPDATE_CHECKOUT  = "UPDATE `" + TABLE_TRACKER + "` SET `checkout` = ? WHERE `id` = ?";
    
    private final SQLiteStatement updateStmtText;
    private static final String   UPDATE_TEXT      = "UPDATE `" + TABLE_TRACKER + "` SET `text` = ? WHERE `id` = ?";
    
    private final SQLiteStatement updateStmtCompany;
    private static final String   UPDATE_COMPANY   = "UPDATE `" + TABLE_TRACKER + "` SET `company` = ? WHERE `id` = ?";
    
    private final SQLiteStatement updateStmtUnread;
    private static final String   UPDATE_UNREAD    = "UPDATE `" + TABLE_TRACKER + "` SET `unread` = ? WHERE `id` = ?";
    
    public DataHelper(Context context)
    {
        this.context = context;
        OpenHelper openHelper = new OpenHelper(this.context);
        
        // Open database
        db = openHelper.getWritableDatabase();
        db.setLocale(Locale.getDefault());
        
        // Setup statements
        insertStmt = db.compileStatement(INSERT);
        updateStmtCheckIn = db.compileStatement(UPDATE_CHECKIN);
        updateStmtCheckOut = db.compileStatement(UPDATE_CHECKOUT);
        updateStmtText = db.compileStatement(UPDATE_TEXT);
        updateStmtCompany = db.compileStatement(UPDATE_COMPANY);
        updateStmtUnread = db.compileStatement(UPDATE_UNREAD);
    }
    
    /**
     * Insert data to database.
     * 
     * @param checkIn
     *            int
     * @param checkOut
     *            int
     * @param text
     *            String
     * @param company
     *            String
     * @return long (insert id)
     * @throws SQLiteException
     */
    public long insert(String company, long checkIn, long checkOut, String text, int unread)
    {
        insertStmt.bindString(1, company);
        insertStmt.bindLong(2, checkIn);
        insertStmt.bindLong(3, checkOut);
        insertStmt.bindString(4, text);
        insertStmt.bindLong(5, unread);
        
        return insertStmt.executeInsert();
    }
    
    /**
     * Updates check in column.
     * 
     * @param checkIn
     *            int
     * @param id
     *            int
     * @throws SQLiteException
     */
    public void updateCheckIn(int checkIn, long id)
    {
        try
        {
            updateStmtCheckIn.bindLong(1, checkIn);
            updateStmtCheckIn.bindLong(2, id);
            
            updateStmtCheckIn.execute();
        }
        // This prevents the application from crashing in case user spams the checkin button
        catch (SQLiteMisuseException e)
        {
            
        }
    }
    
    /**
     * Updates check out column.
     * 
     * @param checkOut
     *            int
     * @param id
     *            int
     * @throws SQLiteException
     */
    public void updateCheckOut(int checkOut, long id)
    {
        try
        {
            updateStmtCheckOut.bindLong(1, checkOut);
            updateStmtCheckOut.bindLong(2, id);
            
            updateStmtCheckOut.execute();
        }
        // This prevents the application from crashing in case user spams the checkout button
        catch (SQLiteMisuseException e)
        {
            
        }
    }
    
    /**
     * Updates text column.
     * 
     * @param text
     *            String
     * @param id
     *            int
     * @throws SQLiteException
     */
    public void updateText(String text, long id)
    {
        try
        {
            updateStmtText.bindString(1, text);
            updateStmtText.bindLong(2, id);
            
            updateStmtText.execute();
        }
        // This prevents the application from crashing in case user spams the save button
        catch (SQLiteMisuseException e)
        {
            
        }
    }
    
    /**
     * Updates company column.
     * 
     * @param company
     *            String
     * @param id
     *            int
     * @throws SQLiteException
     */
    public void updateCompanyName(String company, long id)
    {
        updateStmtCompany.bindString(1, company);
        updateStmtCompany.bindLong(2, id);
        
        updateStmtCompany.execute();
    }
    
    /**
     * Updates the unread column.
     * 
     * @param unread
     *            int
     * @param id
     *            int
     */
    public void updateUnread(int unread, long id)
    {
        updateStmtUnread.bindLong(1, unread);
        updateStmtUnread.bindLong(2, id);
        
        updateStmtUnread.execute();
    }
    
    /**
     * Deletes all data from TABLE_TRACKER.
     * 
     * @see DataHelper#TABLE_TRACKER
     * @throws SQLiteException
     */
    public void deleteAll()
    {
        db.delete(TABLE_TRACKER, null, null);
    }
    
    /**
     * Selects all data within min max range.
     * 
     * @param min
     *            int
     * @param max
     *            int
     * @return Cursor
     */
    public Cursor selectAll(int min, int max)
    {
        // Query
        Cursor c = db.rawQuery("SELECT * FROM " + TABLE_TRACKER + " ORDER BY `id` DESC LIMIT ?, ?", new String[] { Integer.toString(min),
                Integer.toString(max) });
        
        return c;
    }
    
    /**
     * Get data from id.
     * 
     * @param id
     * @return String[] (company, checkin, checkout, text)
     * @throws SQLiteException
     */
    public String[] getInfo(long id)
    {
        // Query
        Cursor c = db.rawQuery("SELECT `company`, `checkin`, `checkout`, `text` FROM " + TABLE_TRACKER + " WHERE `id` = ?",
                new String[] { Long.toString(id) });
        int checkin = 0, checkout = 0;
        String text = "", company = "";
        
        if (c.moveToFirst())
        {
            // Move to id
            company = c.getString(0);
            checkin = c.getInt(1);
            checkout = c.getInt(2);
            text = c.getString(3);
        }
        
        // Close cursor
        if (c != null && !c.isClosed())
            c.close();
        
        // Return it
        return new String[] { company, Integer.toString(checkin), Integer.toString(checkout), text };
    }
    
    /**
     * Closes the database connection.
     */
    public void close()
    {
        if (db != null)
            db.close();
    }
    
    /**
     * Deletes id from the database.
     * 
     * @param id
     *            long
     * @return int
     */
    public int deleteRow(long id)
    {
        return db.delete(TABLE_TRACKER, "`id` = ?", new String[] { Long.toString(id) });
    }
    
    private static class OpenHelper extends SQLiteOpenHelper
    {
        OpenHelper(Context context)
        {
            super(context, DATABASE_NAME, null, DATABASE_VERSION);
        }
        
        @Override
        public void onCreate(SQLiteDatabase db)
        {
            db.execSQL("CREATE TABLE "
                    + TABLE_TRACKER
                    + "(id INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL UNIQUE, company VARCHAR NOT NULL, checkin INTEGER NOT NULL, checkout INTEGER NOT NULL, text TEXT NOT NULL, unread INTEGER NOT NULL DEFAULT 0)");
        }
        
        @Override
        public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion)
        {
            Log.i(Config.APPLICATION_NAME, "Upgrading database from version " + oldVersion + " to " + newVersion);
            
            // Unread column
            db.execSQL("ALTER TABLE " + TABLE_TRACKER + " ADD COLUMN unread INTEGER NOT NULL  DEFAULT 0");
            
            // Create it
            //onCreate(db);
        }
    }
}